﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Utility;
using Portal.Web.Utility;
using Portal.Exam.Model;
using Portal.Core;


namespace Portal.Exam.Bussiness
{
    public class ExamRoomBll
    {
        string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }

        public ExamRoomCls CreateModel(string SiteId, string ExamRoomId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string Query = " select TableExamRoom.*,ExamType,ExamSegmentName from TableExamRoom " +
                           "        join TableExamSegment on TableExamSegment.ExamSegmentId=frkExamSegmentId" +
                           "     where ExamRoomId = '" + ExamRoomId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            if (dtResult.Rows.Count == 0)
            {
                return null;
            }

            ExamRoomCls tExamRoomCls = new ExamRoomCls();
            tExamRoomCls.ExamRoomId = (string)dtResult.Rows[0]["ExamRoomId"];
            tExamRoomCls.RoomNo = (string)dtResult.Rows[0]["RoomNo"];
            tExamRoomCls.ExamSegmentName = (string)dtResult.Rows[0]["ExamSegmentName"];
            tExamRoomCls.BuildingInfo = (string)dtResult.Rows[0]["BuildingInfo"];
            tExamRoomCls.Teacher1 = (string)dtResult.Rows[0]["Teacher1"];
            tExamRoomCls.Teacher2 = (string)dtResult.Rows[0]["Teacher2"];
            tExamRoomCls.Duration = (int)dtResult.Rows[0]["Duration"];
            tExamRoomCls.ActiveRoom = (int)dtResult.Rows[0]["ActiveRoom"];
            tExamRoomCls.OpenForExam = (int)dtResult.Rows[0]["OpenForExam"];
            tExamRoomCls.ExamSegmentId = (string)dtResult.Rows[0]["frkExamSegmentId"];
            tExamRoomCls.AssetCode = (string)dtResult.Rows[0]["AssetCode"];
            tExamRoomCls.ExamSubject = (string)dtResult.Rows[0]["ExamSubject"];
            tExamRoomCls.ExamType = (string)dtResult.Rows[0]["ExamType"];

            if (dtResult.Rows[0]["ExamTitle"] != DBNull.Value)
            {
                tExamRoomCls.ExamTitle = (string)dtResult.Rows[0]["ExamTitle"];
            }
            else
            {
                tExamRoomCls.ExamTitle = tExamRoomCls.ExamSubject;
            }
            if (dtResult.Rows[0]["ExamDescription"] != DBNull.Value)
            {
                tExamRoomCls.ExamDescription = (string)dtResult.Rows[0]["ExamDescription"];
            }

            tExamRoomCls.StartTime = (DateTime)dtResult.Rows[0]["StartTime"];
            if (dtResult.Rows[0]["ExamTemplateId"] != DBNull.Value)
            {
                tExamRoomCls.ExamTemplateId = (string)dtResult.Rows[0]["ExamTemplateId"];
            }
            return tExamRoomCls;
        }


        public ExamRoomCls CreateModel(string SiteId, string ExamRoomSegmentId, string RoomNo)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string Query = " select ExamRoomId from TableExamRoom " +
                           "     where frkExamSegmentId = @ExamRoomSegmentId and RoomNo=@RoomNo";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;
            SqlCommand.Parameters.Add("@ExamRoomSegmentId", SqlDbType.VarChar).Value = ExamRoomSegmentId;
            SqlCommand.Parameters.Add("@RoomNo", SqlDbType.VarChar).Value = RoomNo;
            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            if (dtResult.Rows.Count == 0)
            {
                return null;
            }

            string ExamRoomId = (string)dtResult.Rows[0]["ExamRoomId"];
            ExamRoomCls ExamRoom = CreateModel(SiteId, ExamRoomId);
            return ExamRoom;
        }

        public DataTable ReadingExamRoom(
            string SiteId,
            string AccountType,
            string ExamSegmentId,
            int Active,
            int OpenExam,
            string Keyword,
            string ExamType)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            //string Query = "select * from TableExamRoom,TableExamSegment where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and ActiveRoom=" + Active + " and AccountType='" + AccountType + "'";

            string Query = "select * from TableExamRoom,TableExamSegment where TableExamRoom.AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and ActiveRoom=" + Active + " and TableExamRoom.AccountType='" + AccountType + "'" + " and ExamType = '" + ExamType + "'" + " and frkExamSegmentId = ExamSegmentId";
            if (ExamSegmentId.Trim().Length > 0)
            {
                Query +=
                    " and frkExamSegmentId = '" + ExamSegmentId + "'";
            }

            if (Keyword.Trim().Length > 0)
            {
                Query += " and ExamSubject like N'%" + Keyword + "%'";
            }
            if (OpenExam != -1)
            {
                Query += " and OpenForExam=" + OpenExam;
            }
            Query += " order by [StartTime]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingExamRoom(
            string SiteId,
            string Keyword,
            int Active = 0)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select * from TableExamRoom where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "'";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and RoomNo like N'%" + Keyword + "%'";
            }
            
            if (Active == 1)
            {
                Query += " and ActiveRoom = " + Active + "";
            }

            Query += " order by [StartTime]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingExamRoomByExamSegmentId(
            string SiteId,
            string ExamSegmentId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (ExamSegmentId == null) ExamSegmentId = "";
            DataTable dataTable = new DataTable();            
            string Query = "select * from TableExamRoom where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "'";
            if (ExamSegmentId.Trim().Length > 0)
            {
                Query += " and frkExamSegmentid ='" + ExamSegmentId + "'";
            }

            Query += " order by [StartTime]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }
        public string CreateExamRoom(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string AccountType,
            string RoomNo,
            string ExamSubject,
            string BuildingInfo,
            string Teacher1,
            string Teacher2,
            DateTime StartTime,
            int Duration,
            int ActiveRoom,
            string ExamSegmentId,
            string ExamTitle,
            string ExamDescription,
            string AssetCode)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewExamRoomId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableExamRoom(ExamRoomId, RoomNo, ExamSubject, BuildingInfo, Teacher1, Teacher2, StartTime, Duration, ActiveRoom, frkExamSegmentId, AssetCode, OpenForExam, ExamTitle, ExamDescription,AccountType) values (@ExamRoomId, @RoomNo, @ExamSubject, @BuildingInfo, @Teacher1, @Teacher2, @StartTime, @Duration, @ActiveRoom, @frkExamSegmentId, @AssetCode, 0, @ExamTitle, @ExamDescription,@AccountType)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamRoomId"].Value = NewExamRoomId;

            TempSqlCommand.Parameters.Add("@RoomNo", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@RoomNo"].Value = RoomNo;

            TempSqlCommand.Parameters.Add("@ExamSubject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamSubject"].Value = ExamSubject;

            TempSqlCommand.Parameters.Add("@BuildingInfo", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@BuildingInfo"].Value = BuildingInfo;


            TempSqlCommand.Parameters.Add("@Teacher1", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Teacher1"].Value = Teacher1;

            TempSqlCommand.Parameters.Add("@Teacher2", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Teacher2"].Value = Teacher2;

            TempSqlCommand.Parameters.Add("@StartTime", SqlDbType.DateTime);
            TempSqlCommand.Parameters["@StartTime"].Value = StartTime;

            TempSqlCommand.Parameters.Add("@Duration", SqlDbType.Int);
            TempSqlCommand.Parameters["@Duration"].Value = Duration;


            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;

            TempSqlCommand.Parameters.Add("@ActiveRoom", SqlDbType.Int);
            TempSqlCommand.Parameters["@ActiveRoom"].Value = 1;

            TempSqlCommand.Parameters.Add("@frkExamSegmentId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkExamSegmentId"].Value = ExamSegmentId;

            TempSqlCommand.Parameters.Add("@ExamTitle", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamTitle"].Value = ExamTitle;

            TempSqlCommand.Parameters.Add("@ExamDescription", SqlDbType.NText);
            TempSqlCommand.Parameters["@ExamDescription"].Value = ExamDescription;

            TempSqlCommand.Parameters.Add("@AccountType", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AccountType"].Value = AccountType;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewExamRoomId;
        }

        public void SetStatus(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomId,
            int Active)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Update TableExamRoom set [ActiveRoom]=@ActiveRoom where ExamRoomId=@ExamRoomId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamRoomId"].Value = ExamRoomId;

            TempSqlCommand.Parameters.Add("@ActiveRoom", SqlDbType.Int);
            TempSqlCommand.Parameters["@ActiveRoom"].Value = Active;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }



        public void SetOpenExam(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomId,
            int OpenForExam)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Update TableExamRoom set [OpenForExam]=@OpenForExam where ExamRoomId=@ExamRoomId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamRoomId"].Value = ExamRoomId;

            TempSqlCommand.Parameters.Add("@OpenForExam", SqlDbType.Int);
            TempSqlCommand.Parameters["@OpenForExam"].Value = OpenForExam;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void SetExamTemplate(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomId,
            string ExamTemplateId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            string QueryTemplate = "SELECT Category FROM TableExamTemplate where ExamTemplateId='" + ExamTemplateId + "'";
            string ExamTemplateType = "";

            SqlConnection TempSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            TempSqlConnection.Open();
            SqlCommand tSqlCommand = TempSqlConnection.CreateCommand();
            tSqlCommand.CommandText = QueryTemplate;
            object OExamTemplateType = tSqlCommand.ExecuteScalar();

            if (OExamTemplateType != null)
                ExamTemplateType = tSqlCommand.ExecuteScalar().ToString();

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = " Update TableExamRoom set ExamTemplateId=@ExamTemplateId,ExamTemplateType=@ExamTemplateType where ExamRoomId=@ExamRoomId";

            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamRoomId"].Value = ExamRoomId;

            TempSqlCommand.Parameters.Add("@ExamTemplateId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamTemplateId"].Value = ExamTemplateId;

            TempSqlCommand.Parameters.Add("@ExamTemplateType", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamTemplateType"].Value = ExamTemplateType;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void UpdateExamRoom(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomId,
            string RoomNo,
            string ExamSubject,
            string BuildingInfo,
            string Teacher1,
            string Teacher2,
            DateTime StartTime,
            int Duration,
            int ActiveRoom,
            string ExamTitle,
            string ExamDescription)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamRoom set RoomNo=@RoomNo, ExamSubject=@ExamSubject, BuildingInfo=@BuildingInfo,Teacher1=@Teacher1,Teacher2=@Teacher2,StartTime=@StartTime,Duration=@Duration,ActiveRoom=@ActiveRoom,ExamTitle=@ExamTitle, ExamDescription=@ExamDescription where ExamRoomId=@ExamRoomId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamRoomId"].Value = ExamRoomId;

            TempSqlCommand.Parameters.Add("@RoomNo", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@RoomNo"].Value = RoomNo;


            TempSqlCommand.Parameters.Add("@BuildingInfo", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@BuildingInfo"].Value = BuildingInfo;

            TempSqlCommand.Parameters.Add("@ExamSubject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamSubject"].Value = ExamSubject;


            TempSqlCommand.Parameters.Add("@Teacher1", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Teacher1"].Value = Teacher1;

            TempSqlCommand.Parameters.Add("@Teacher2", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Teacher2"].Value = Teacher2;

            TempSqlCommand.Parameters.Add("@StartTime", SqlDbType.DateTime);
            TempSqlCommand.Parameters["@StartTime"].Value = StartTime;

            TempSqlCommand.Parameters.Add("@Duration", SqlDbType.Int);
            TempSqlCommand.Parameters["@Duration"].Value = Duration;

            TempSqlCommand.Parameters.Add("@ActiveRoom", SqlDbType.Int);
            TempSqlCommand.Parameters["@ActiveRoom"].Value = ActiveRoom;


            TempSqlCommand.Parameters.Add("@ExamTitle", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamTitle"].Value = ExamTitle;

            TempSqlCommand.Parameters.Add("@ExamDescription", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamDescription"].Value = ExamDescription;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void DeleteExamRoom(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Delete TableExamRoom where ExamRoomId=@ExamRoomId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamRoomId"].Value = ExamRoomId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }
    }
}
